package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.StudentClass;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class StudentClassDao {

    private static StudentClassDao graduateProjectSourceDao = null;

    public static StudentClassDao getInstance() {
        if (graduateProjectSourceDao == null) {
            graduateProjectSourceDao = new StudentClassDao();
        }
        return graduateProjectSourceDao;
    }

    public static Collection<StudentClass> findAll(List conditionList,
                                                   Pagination pagination,
                                                   Connection connection)
            throws SQLException {
        Collection<StudentClass> graduateProjectSources = new TreeSet<StudentClass>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection, "StudentClass");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM StudentClass ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            graduateProjectSources.add(new StudentClass(resultSet.getInt("id"),
                    resultSet.getString("no"),
                    resultSet.getString("description"),
                    resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(resultSet.getInt("department_id"))
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectSources;
    }

    public static StudentClass find(int id, Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from StudentClass where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        StudentClass graduateProjectSource = null;
        //游标下移
        if (resultSet.next()) {
            //创建StudentClass对象，各字段为结果集中相应字段
            graduateProjectSource = new StudentClass(resultSet.getInt("id"),
                    resultSet.getString("no"),
                    resultSet.getString("description"),
                    resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(resultSet.getInt("department_id"))
            );

        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectSource;
    }
}


